package jwr.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class J23_MemberDAO implements J23_MemberService{
	
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String INSERTSQL="insert into member(userid,jumin,pwd,zip,addr,tel,mp,pay) values(member_userid.nextval,?,?,?,?,?,?,?)";
	private static final String UPDATESQL="update member set pwd = ?, zip = ?, addr = ?, tel = ?, mp = ?, pay = ? where userid = ?";
	private static final String DELETESQL="delete from member where userid = ?";
	private static final String SELECTSQL="select userid,jumin,mp from member order by userid desc";
	private static final String SELECTONESQL="select * from member where userid = ?";
	
	private static Connection conn = null;
	private static PreparedStatement ps = null;
	
	@Override
	public int insertMember(J23_MemberVO mb) {
		int cnt =0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(INSERTSQL);
				ps.setString(1, mb.getJumin());
				ps.setString(2, mb.getPwd());
				ps.setString(3, mb.getZip());
				ps.setString(4, mb.getAddr());
				ps.setString(5, mb.getTel());
				ps.setString(6, mb.getMp());
				ps.setString(7, mb.getPay());
			cnt = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ ps.close(); } catch(Exception e){ }
			if(conn!=null) try{ conn.close(); } catch(Exception e){ }
		}
		return cnt;
	}

	@Override
	public int updateMember(J23_MemberVO mb) {
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(UPDATESQL);
				ps.setString(1, mb.getPwd());
				ps.setString(2, mb.getZip());
				ps.setString(3, mb.getAddr());
				ps.setString(4, mb.getTel());
				ps.setString(5, mb.getMp());
				ps.setString(6, mb.getPay());
				ps.setString(7, mb.getUserid());
			cnt = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ ps.close(); } catch(Exception e){ }
			if(conn!=null) try{ conn.close(); } catch(Exception e){ }
		}
		return cnt;
	}

	@Override
	public int deleteMember(String userid) {
		int cnt = 0;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(DELETESQL);
				ps.setString(1, userid);
			cnt = ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(ps!=null) try{ ps.close(); } catch(Exception e){ }
			if(conn!=null) try{ conn.close(); } catch(Exception e){ }
		}
		return cnt;
	}

	@Override
	public ArrayList<J23_MemberVO> selectMember() {
		ArrayList<J23_MemberVO> data = new ArrayList<J23_MemberVO>();
		J23_MemberVO mb=null;
		ResultSet rs = null;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SELECTSQL);
			rs = ps.executeQuery();
			while(rs.next()){
				mb = new J23_MemberVO(rs.getString(1), rs.getString(2), "", "", "", "", rs.getString(3), "");
				data.add(mb);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{ rs.close(); } catch(Exception e){ }
			if(ps != null) try{ ps.close(); } catch(Exception e){ }
			if(conn != null) try{ conn.close(); } catch(Exception e){ }
		}
		return data;
	}

	@Override
	public J23_MemberVO selectOneMember(String userid) {
		J23_MemberVO mb = null;
		ResultSet rs = null;
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SELECTONESQL);
				ps.setString(1, userid);
			rs = ps.executeQuery();
			while(rs.next()){
				mb = new J23_MemberVO(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8));
				mb.setPoint(rs.getInt(9));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{ rs.close(); } catch(Exception e){ }
			if(ps != null) try{ ps.close(); } catch(Exception e){ }
			if(conn != null) try{ conn.close(); } catch(Exception e){ }
		}
		
		return mb;
	}

}
